﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;

namespace ExcelToSql.Code
{
    class ExcelProcessor
    {
        string _fileName;

        string[] _sheetNames;
        Workbook _wb = null;
        List<DataColumnDef> _colDefs = new List<DataColumnDef>();
        Microsoft.Office.Interop.Excel.Application _xlApp = null;



        public string[] SheetNames
        {
            get { return _sheetNames; }
        }

        public string FileName
        {
            get { return _fileName; }
        }

        public List<DataColumnDef> ColDefs
        {
            get { return _colDefs; }
        }

        public ExcelProcessor(string fileName)
        {
            _fileName = fileName;
        }



        public void OpenFile()
        {
            _xlApp = new Microsoft.Office.Interop.Excel.Application();

            _wb = _xlApp.Workbooks.Open(_fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            _sheetNames = new string[_wb.Worksheets.Count];

            for (int i = 0; i < _wb.Worksheets.Count; i++)
                _sheetNames[i] = ((Worksheet)_wb.Worksheets[i + 1]).Name;

        }

        public System.Data.DataTable ParseWorksheet(string worksheetName)
        {
            Worksheet wsActive = null;
            Range excelRange;
            DataImportTable dtImport;
            Type[] types;
            object[,] valueArray;

            // Check that the file has been opened, and that the worksheet exists
            if (_wb == null)
                throw new ApplicationException("You must invoke OpenFile before using this function.");
            try
            {
                wsActive = (Worksheet)_wb.Worksheets[worksheetName];
            }
            catch (Exception)
            {
                throw new ArgumentException("The worksheet name does not exist.");
            }

            // Tip from: http://www.dotnetperls.com/excel
            excelRange = wsActive.UsedRange;
            valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

            foreach (Workbook wb in _xlApp.Workbooks)
                wb.Close(false, Type.Missing, Type.Missing);
            _xlApp.Quit();
            _xlApp = null;

            dtImport = new DataImportTable();

            // Find the DataColumns            
            for (int i = 1; i <= valueArray.GetUpperBound(1); i++)
            {
                string colName = "Column_" + i;

                try
                {
                    colName = valueArray[1, i].ToString().Trim();
                }
                catch { }

                string baseColName = null;
                int suffix = 0;

                if (String.IsNullOrEmpty(colName))
                    colName = "UnnamedColumn";

                colName = Regex.Replace(colName, "[^a-zA-Z0-9]", "_");

                baseColName = colName;

                // Ensure that the column name is unique; if not, append a numeric suffix to the end
                while (dtImport.Columns.IndexOf(colName) > -1)
                {
                    suffix++;
                    colName = baseColName + "_" + suffix.ToString();
                }

                dtImport.Columns.Add(new System.Data.DataColumn(colName, typeof(String)));

            }

            // Read the file data
            for (int r = 2; r <= valueArray.GetUpperBound(0); r++)
            {
                System.Data.DataRow dr = dtImport.NewRow();

                for (int c = 0; c < dtImport.Columns.Count; c++)
                    dr[c] = valueArray[r, c + 1];
                dtImport.Rows.Add(dr);
            }


            // Work out the Column types
            types = new Type[dtImport.Columns.Count];
            for (int i = 0; i < dtImport.Columns.Count; i++)
            {
                int colSize = 0;
                string colName = dtImport.Columns[i].ColumnName;
                DataColumnDef newDef;

                types[i] = dtImport.GetColumnType(i, ref colSize);
                newDef = DataColumnDef.ParseFromType(types[i]);
                newDef.Name = colName;
                newDef.SqlLength = colSize;
                _colDefs.Add(newDef);
            }

            // Conver the data to a DataTable with strongly-typed columns
            System.Data.DataTable dtFinal = dtImport.ToDataTable(types);


            return dtFinal;
        }

    }
}
